package carassius.DAL;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 *
 * @author siebz0r
 */
public final class Database
{
	//<editor-fold defaultstate="collapsed" desc="Fields">
	private static Connection _connection;
	private static AquariumTypeTable _aquariumTypeTable;
	private static AquariumTable _aquariumTable;
	private static WaterRefreshmentTable _waterRefreshmentTable;
	private static WaterMeasurementTable _waterMeasurementTable;
	private static WaterConditionTable _waterConditionTable;
	private static WaterMeasurementListTable _waterMeasurementListTable;
	private static OptimalAquariumWaterConditionTable _optimalAquariumWaterConditionTable;
	private static PictureTable _pictureTable;
	private static AnimalTable _animalTable;
	private static PlantTable _plantTable;
	private static OrganismListTable _organismListTable;
	private static OptimalOrganismWaterConditionTable _optimalOrganismWaterConditionTable;
	//</editor-fold>

	public static synchronized void initDatabase(String dbpath) throws ClassNotFoundException, SQLException
	{
		if (_connection == null)
		{
			//initialize hsqldb class (throws ClassNotFountException)
			Class.forName("org.hsqldb.jdbcDriver");

			StringBuilder sb = new StringBuilder();
			sb.append("jdbc:hsqldb:");
			sb.append("file:");
			sb.append(dbpath);

			//connect to database (throws SQLException)
			_connection = DriverManager.getConnection(sb.toString());
			
			_aquariumTypeTable = new AquariumTypeTable();
			_aquariumTable = new AquariumTable();
			_waterRefreshmentTable = new WaterRefreshmentTable();
			_waterMeasurementTable = new WaterMeasurementTable();
			_waterConditionTable = new WaterConditionTable();
			_waterMeasurementListTable = new WaterMeasurementListTable();
			_optimalAquariumWaterConditionTable = new OptimalAquariumWaterConditionTable();
			_pictureTable = new PictureTable();
			_animalTable = new AnimalTable();
			_plantTable = new PlantTable();
			_organismListTable = new OrganismListTable();
			_optimalOrganismWaterConditionTable = new OptimalOrganismWaterConditionTable();

			createDB();
		}
	}

	//<editor-fold defaultstate="collapsed" desc="Public Properties">
	public static AquariumTypeTable getAquariumTypeTable()
	{
		return _aquariumTypeTable;
	}

	public static AquariumTable getAquariumTable()
	{
		return _aquariumTable;
	}

	public static WaterRefreshmentTable getWaterRefreshmentTable()
	{
		return _waterRefreshmentTable;
	}

	public static WaterMeasurementTable getWaterMeasurementTable()
	{
		return _waterMeasurementTable;
	}

	public static WaterConditionTable getWaterConditionTable()
	{
		return _waterConditionTable;
	}

	public static WaterMeasurementListTable getWaterMeasurementListTable()
	{
		return _waterMeasurementListTable;
	}

	public static OptimalAquariumWaterConditionTable getOptimalAquariumWaterConditionTable()
	{
		return _optimalAquariumWaterConditionTable;
	}

	public static PictureTable getPictureTable()
	{
		return _pictureTable;
	}

	public static AnimalTable getAnimalTable()
	{
		return _animalTable;
	}

	public static PlantTable getPlantTable()
	{
		return _plantTable;
	}

	public static OrganismListTable getOrganismListTable()
	{
		return _organismListTable;
	}

	public static OptimalOrganismWaterConditionTable getOptimalOrganismWaterConditionTable()
	{
		return _optimalOrganismWaterConditionTable;
	}
	//</editor-fold>

	//<editor-fold defaultstate="collapsed" desc="Public Methods">
	/**
	 * Closes the database connection. Cannot be reopened!
	 * @throws SQLException
	 */
	public static synchronized void close() throws SQLException
	{
		Statement st = _connection.createStatement();
		st.execute("SHUTDOWN");
		_connection.close();
	}

	/**
	 * Creates the database
	 * @throws SQLException
	 */
	public static synchronized void createDB() throws SQLException
	{
		//<editor-fold defaultstate="collapsed" desc="aquarium_type">
		if (!tableExists("aquarium_type"))
		{
			PreparedStatement st = prepareStatement("CREATE TABLE aquarium_type ( id INT IDENTITY,"
					+ "name VARCHAR(20) NOT NULL)");
			try
			{
				st.executeUpdate();
			}
			catch (SQLException ex)
			{
				throw ex;
			}
			finally
			{
				st.close();
			}
		}
		//</editor-fold>

		//<editor-fold defaultstate="collapsed" desc="aquarium">
		if (!tableExists("aquarium"))
		{
			PreparedStatement st = prepareStatement("CREATE TABLE aquarium ( id INT IDENTITY,"
					+ "name VARCHAR(20) NOT NULL,"
					+ "length INT,"
					+ "width INT,"
					+ "height INT,"
					+ "type INT,"
					+ "lighting VARCHAR(500),"
					+ "heating VARCHAR(500),"
					+ "filtering VARCHAR(500),"
					+ "CO2 VARCHAR(500),"
					+ "FOREIGN KEY ( type ) REFERENCES aquarium_type(id) )");
			try
			{
				st.executeUpdate();
			}
			catch (SQLException ex)
			{
				throw ex;
			}
			finally
			{
				st.close();
			}
		}
		//</editor-fold>

		//<editor-fold defaultstate="collapsed" desc="action">
		if (!tableExists("action"))
		{
			PreparedStatement st = prepareStatement("CREATE TABLE action ( id INT IDENTITY,"
					+ "date DATE,"
					+ "comment VARCHAR(500),"
					+ "aquarium_id INT,"
					+ "FOREIGN KEY ( aquarium_id ) REFERENCES aquarium(id) )");
			try
			{
				st.executeUpdate();
			}
			catch (SQLException ex)
			{
				throw ex;
			}
			finally
			{
				st.close();
			}
		}
		//</editor-fold>

		//<editor-fold defaultstate="collapsed" desc="water_refreshment">
		if (!tableExists("water_refreshment"))
		{
			PreparedStatement st = prepareStatement("CREATE TABLE water_refreshment ( id INT,"
					+ "PRIMARY KEY ( id ),"
					+ "FOREIGN KEY ( id ) REFERENCES action(id) )");
			try
			{
				st.executeUpdate();
			}
			catch (SQLException ex)
			{
				throw ex;
			}
			finally
			{
				st.close();
			}
		}
		//</editor-fold>

		//<editor-fold defaultstate="collapsed" desc="water_measurement">
		if (!tableExists("water_measurement"))
		{
			PreparedStatement st = prepareStatement("CREATE TABLE water_measurement ( id INT,"
					+ "PRIMARY KEY ( id ),"
					+ "FOREIGN KEY ( id ) REFERENCES action(id) )");
			try
			{
				st.executeUpdate();
			}
			catch (SQLException ex)
			{
				throw ex;
			}
			finally
			{
				st.close();
			}
		}
		//</editor-fold>

		//<editor-fold defaultstate="collapsed" desc="water_condition">
		if (!tableExists("water_condition"))
		{
			PreparedStatement st = prepareStatement("CREATE TABLE water_condition ( name VARCHAR(5),"
					+ "description VARCHAR(100),"
					+ "PRIMARY KEY ( name ) )");
			try
			{
				st.executeUpdate();
			}
			catch (SQLException ex)
			{
				throw ex;
			}
			finally
			{
				st.close();
			}
		}
		//</editor-fold>

		//<editor-fold defaultstate="collapsed" desc="water_measurement_list">
		if (!tableExists("water_measurement_list"))
		{
			PreparedStatement st = prepareStatement("CREATE TABLE water_measurement_list ("
					+ "measurement_id INT,"
					+ "condition VARCHAR(5),"
					+ "value DOUBLE NOT NULL,"
					+ "PRIMARY KEY ( measurement_id, condition ),"
					+ "FOREIGN KEY ( measurement_id ) REFERENCES water_measurement(id),"
					+ "FOREIGN KEY ( condition ) REFERENCES water_condition(name) )");
			try
			{
				st.executeUpdate();
			}
			catch (SQLException ex)
			{
				throw ex;
			}
			finally
			{
				st.close();
			}
		}
		//</editor-fold>

		//<editor-fold defaultstate="collapsed" desc="optimal_aquarium_water_condition">
		if (!tableExists("optimal_aquarium_water_condition"))
		{
			PreparedStatement st = prepareStatement("CREATE TABLE optimal_aquarium_water_condition ( aquarium_id INT,"
					+ "water_condition VARCHAR(5),"
					+ "min_value DOUBLE,"
					+ "max_value DOUBLE,"
					+ "PRIMARY KEY (aquarium_id, water_condition),"
					+ "FOREIGN KEY ( aquarium_id ) REFERENCES aquarium(id),"
					+ "FOREIGN KEY ( water_condition ) REFERENCES water_condition(name) )");
			try
			{
				st.executeUpdate();
			}
			catch (SQLException ex)
			{
				throw ex;
			}
			finally
			{
				st.close();
			}
		}
		//</editor-fold>

		//<editor-fold defaultstate="collapsed" desc="picture">
		if (!tableExists("picture"))
		{
			PreparedStatement st = prepareStatement("CREATE TABLE picture ( id INT IDENTITY,"
					+ "path VARCHAR(500) NOT NULL,"
					+ "name VARCHAR(500),"
					+ "comment VARCHAR(500),"
					+ "UNIQUE (path),"
					+ ")");
			try
			{
				st.executeUpdate();
			}
			catch (SQLException ex)
			{
				throw ex;
			}
			finally
			{
				st.close();
			}
		}
		else
		{
			PreparedStatement id = prepareStatement("SELECT COLUMN_NAME FROM information_schema.columns WHERE TABLE_NAME = 'PICTURE' AND COLUMN_NAME = 'ID'");
			try
			{
				ResultSet idQuery = id.executeQuery();
				if (!idQuery.next())
				{
					//modify table
					Statement modTable = _connection.createStatement();
					modTable.addBatch("ALTER TABLE picture RENAME TO picture_old");
					modTable.addBatch("CREATE TABLE picture ( id INT IDENTITY,"
							+ "path VARCHAR(500) NOT NULL,"
							+ "name VARCHAR(500),"
							+ "comment VARCHAR(500),"
							+ "UNIQUE (path),"
							+ ")");
					modTable.executeBatch();
					modTable.close();

					//transfer old pictures to new table
					PreparedStatement st = prepareStatement("SELECT * FROM picture_old");
					try
					{
						ResultSet oldrows = st.executeQuery();
						while (oldrows.next())
						{
							String path = oldrows.getString("path");
							String name = oldrows.getString("name");
							String comment = oldrows.getString("comment");
							PreparedStatement newrow = prepareStatement("INSERT INTO picture VALUES (NULL , ? , ? , ? )");
							newrow.setString(1, path);
							newrow.setObject(2, name, java.sql.Types.VARCHAR);
							newrow.setObject(3, comment, java.sql.Types.VARCHAR);
							newrow.executeUpdate();
							newrow.close();
						}
					}
					catch (SQLException ex)
					{
						System.out.println("Couldn't transfer pictures from old to new table!");
						throw ex;
					}
					finally
					{
						st.close();
					}

					//modify organism table if needed
					st = prepareStatement("SELECT COLUMN_NAME FROM information_scheme.column_names WHERE table_name = 'ORGANISM' AND COLUMN_NAME = 'PICTURE_ID'");
					try
					{
						idQuery = st.executeQuery();
						if (!idQuery.next())
						{
							modTable = _connection.createStatement();
							modTable.addBatch("ALTER TABLE organism ADD COLUMN picture_id INT");
							modTable.addBatch("ALTER TABLE organism ADD FOREIGN KEY (picture_id) REFERENCES picture(id)");
							modTable.addBatch("UPDATE organism SET picture_id = (SELECT id FROM picture WHERE path = picture)");
							modTable.addBatch("ALTER TABLE organism DROP COLUMN picture CASCADE");
							modTable.addBatch("DROP TABLE picture_old");
							modTable.executeBatch();
							modTable.close();
						}
					}
					catch (SQLException ex)
					{
						throw ex;
					}
					finally
					{
						st.close();
					}
				}
				else
				{
					PreparedStatement thumbnail = prepareStatement("SELECT COLUMN_NAME FROM information_schema.columns WHERE TABLE_NAME = 'PICTURE' AND COLUMN_NAME = 'THUMBNAIL'");
					try
					{
						ResultSet thumbnailQuery = thumbnail.executeQuery();
						if (thumbnailQuery.next())
						{
							//thumbnail exists and needs to be dropped
							PreparedStatement dropThumbnail = prepareStatement("ALTER TABLE picture DROP COLUMN thumbnail CASCADE");
							try
							{
								dropThumbnail.executeUpdate();
							}
							catch (SQLException ex)
							{
								throw ex;
							}
							finally
							{
								dropThumbnail.close();
							}
						}
					}
					catch (SQLException ex)
					{
						throw ex;
					}
					finally
					{
						thumbnail.close();
					}
				}
			}
			catch (SQLException ex)
			{
				throw ex;
			}
			finally
			{
				id.close();
			}
		}
		//</editor-fold>

		//<editor-fold defaultstate="collapsed" desc="organism">
		if (!tableExists("organism"))
		{
			PreparedStatement st = prepareStatement("CREATE TABLE organism ("
					+ "id INT IDENTITY,"
					+ "scientific_name VARCHAR(50),"
					+ "alternative_name VARCHAR(50),"
					+ "description VARCHAR(500),"
					+ "min_temperature INT,"
					+ "max_temperature INT,"
					+ "max_size INT,"
					//					+ "difficulty INT," //todo: drop this shit
					+ "picture_id INT,"
					+ "FOREIGN KEY (picture_id) REFERENCES picture(id) )");
			try
			{
				st.executeUpdate();
			}
			catch (SQLException ex)
			{
				throw ex;
			}
			finally
			{
				st.close();
			}
		}
		else
		{
			PreparedStatement st = prepareStatement("SELECT COLUMN_NAME FROM information_schema.columns WHERE TABLE_NAME = 'ORGANISM' AND COLUMN_NAME = 'DIFFICULTY'");
			try
			{
				ResultSet query = st.executeQuery();
				if (query.next())
				{
					//difficulty exists
					PreparedStatement dropDifficulty = prepareStatement("ALTER TABLE organism DROP COLUMN difficulty");
					try
					{
						dropDifficulty.executeUpdate();
					}
					catch (SQLException ex)
					{
						throw ex;
					}
					finally
					{
						dropDifficulty.close();
					}
				}
			}
			catch (SQLException ex)
			{
				throw ex;
			}
			finally
			{
				st.close();
			}
		}
		//</editor-fold>

		//<editor-fold defaultstate="collapsed" desc="optimal_organism_water_condition">
		if (!tableExists("optimal_organism_water_condition"))
		{
			PreparedStatement st = prepareStatement("CREATE TABLE optimal_organism_water_condition ( organism_id INT,"
					+ "water_condition VARCHAR(5),"
					+ "min_value DOUBLE,"
					+ "max_value DOUBLE,"
					+ "PRIMARY KEY ( organism_id, water_condition ),"
					+ "FOREIGN KEY ( organism_id ) REFERENCES organism(id),"
					+ "FOREIGN KEY ( water_condition ) REFERENCES water_condition(name) )");
			try
			{
				st.executeUpdate();
			}
			catch (SQLException ex)
			{
				throw ex;
			}
			finally
			{
				st.close();
			}
		}
		//</editor-fold>

		//<editor-fold defaultstate="collapsed" desc="organism_list">
		if (!tableExists("organism_list"))
		{
			PreparedStatement st = prepareStatement("CREATE TABLE organism_list ( aquarium_id INT,"
					+ "organism_id INT,"
					+ "amount INT,"
					+ "PRIMARY KEY ( aquarium_id, organism_id ),"
					+ "FOREIGN KEY ( aquarium_id ) REFERENCES aquarium(id),"
					+ "FOREIGN KEY ( organism_id ) REFERENCES organism(id) )");
			try
			{
				st.executeUpdate();
			}
			catch (SQLException ex)
			{
				throw ex;
			}
			finally
			{
				st.close();
			}
		}
		//</editor-fold>

		//<editor-fold defaultstate="collapsed" desc="plant">
		if (!tableExists("plant"))
		{
			PreparedStatement st = prepareStatement("CREATE TABLE plant ( id INT,"
					+ "light INT,"
					+ "PRIMARY KEY ( id ),"
					+ "FOREIGN KEY ( id ) REFERENCES organism(id) )");
			try
			{
				st.executeUpdate();
			}
			catch (SQLException ex)
			{
				throw ex;
			}
			finally
			{
				st.close();
			}
		}
		//</editor-fold>

		//<editor-fold defaultstate="collapsed" desc="animal">
		if (!tableExists("animal"))
		{
			PreparedStatement st = prepareStatement("CREATE TABLE animal ( id INT,"
					+ "feed VARCHAR(500),"
					+ "aquarium_length INT,"
					+ "aquarium_height INT,"
					+ "aquarium_liter INT,"
					+ "breed VARCHAR(500),"
					+ "origin VARCHAR(100),"
					+ "schooling BIT,"
					+ "FOREIGN KEY ( id ) REFERENCES organism(id) )");
			try
			{
				st.executeUpdate();
			}
			catch (SQLException ex)
			{
				throw ex;
			}
			finally
			{
				st.close();
			}
		}
		//</editor-fold>
	}

	private static boolean tableExists(String name) throws SQLException
	{
		boolean result = false;
		if (name != null && !name.isEmpty())
		{
			PreparedStatement st = prepareStatement("SELECT table_name FROM information_schema.tables WHERE table_name = ?");
			st.setString(1, name.toUpperCase());
			try
			{
				ResultSet query = st.executeQuery();
				if (query.next())
				{
					result = true;
				}
			}
			catch (SQLException ex)
			{
				throw ex;
			}
			finally
			{
				st.close();
			}
		}
		return result;
	}

	public static PreparedStatement prepareStatement(String sql) throws SQLException
	{
		return _connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
	}
	//</editor-fold>
}
